﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HTComics.Data.Tools
{
    public class DataTester
    {
        public static DataTable GetDuplicateIssues(string connString)
        {
            SqlConnection conn;
            SqlCommand cmd;
            DataTable dt = new DataTable();

            conn = new SqlConnection(connString);
            conn.Open();
            cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = "SELECT ComicId, IssueNumber, ConditionId, COUNT(*) FROM TBL_Issue Group By ComicId, IssueNumber, ConditionId Having COUNT(*) > 1";
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

        public static DataTable GetActualDuplicateIssues(string connString, string comicId, string issue, string conditionId)
        {
            SqlConnection conn;
            SqlCommand cmd;
            DataTable dt = new DataTable();

            conn = new SqlConnection(connString);
            conn.Open();
            cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = "SELECT * FROM TBL_Issue I WHERE I.ComicId = " + comicId + " AND I.IssueNumber = '" + issue + "' AND I.ConditionId = " + conditionId + " ORDER BY IssueId";
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

        public static string GetSql(DataTable dtDupIssues)
        {
            int minId = -1;
            int qty = 0;
            string sql1 = "";
            string sql2 = "";

            minId = Convert.ToInt32(dtDupIssues.Rows[0]["IssueId"].ToString());

            for (int i = 0; i <= dtDupIssues.Rows.Count - 1; i++)
            {
                if (minId != Convert.ToInt32(dtDupIssues.Rows[i]["IssueId"].ToString()))
                {
                    sql1 += "DELETE FROM TBL_Issue WHERE IssueId = " + dtDupIssues.Rows[i]["IssueId"].ToString() + "\n";
                    qty += Convert.ToInt32(dtDupIssues.Rows[i]["Quantity"].ToString());
                }
            }

            sql2 = "UPDATE Tbl_Issue SET Quantity = Quantity + " + qty + " WHERE IssueId = " + minId;

            return sql2 + "\n" + sql1 + "\n\n";
        }

    }
}
